﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.project.dao.impl;

import java.util.*;

import antlr.debug.DebuggingCharScanner;

import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.project.dao.*;
import com.hh.bss.project.entity.*;
import com.hh.bss.project.service.*;
import com.hh.bss.project.vo.query.*;
import com.hh.bss.talent.entity.Cv;
import com.hh.bss.talent.entity.CvContactDetail;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class SearchProjectCcListDaoImpl extends BaseHibernateDao<SearchProjectCcList,java.lang.Integer>  implements SearchProjectCcListDao<SearchProjectCcList,java.lang.Integer>{

	public Class getEntityClass() {
		return SearchProjectCcList.class;
	}
	public Page findCcPage(SearchProjectCcListQuery query){
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		//sql.append(" select concat(ifnull(a.ref_cvid,0),ifnull(a.company_id,0)) id,a.project_id projectId,a.company_id companyId,a.call_dt callDt,'' cvSearchStatus,b1.name companyName,a.ref_cvid cvId,b.cv_name cvName,f.mobile,h.phone,c.deptName deptName,d.titleName titleName,e.current_salary currentSalary,i.email,j.tag,b.sex,b.function,b.level,b.degree,b.work_year workYear,b.expect_location expectLocation from (select project_id,company_id,dept_id,title_id,ref_cvid,max(call_dt) call_dt from company_cc  group by company_id,dept_id,title_id,project_id,ref_cvid) a ");
		sql.append(" select a.project_id projectId,a.id,a.call_dt callDt,a.cc_type ccType,a.cc_info ccInfo,a.account,a.caller,a.callno,a.call_status callStatus,a.cc_result ccResult,a.ref_cvid refCvid,b.cv_name cvName,a.comment,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt from  search_project_cc_list a ");
		sql.append(" left join  cv b on a.ref_cvid=b.id and b.is_delete=0 ");
		//sql.append(" left join  company b1 on a.company_id=b1.id ");
		//sql.append(" left join (select t1.cv_id,min(t2.name) deptName from company_cv t1,company_dept t2 where t1.dept_id=t2.id group by t1.cv_id) c on a.ref_cvid=c.cv_id ");
		//sql.append(" left join company_dept c on a.dept_id=c.id ");
		//sql.append(" left join company_dept_title d on a.title_id=d.id ");		
		//sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as mobile from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_MOBILE).append("' group by cv_id) f on a.ref_cvid=f.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((concat(if(nbr_ext1 is null,'','('),ifnull(nbr_ext1,''),if(nbr_ext1 is null,'',')'),ifnull(nbr,''),if(nbr_ext2 is null,'','-'),ifnull(nbr_ext2,''))) separator ',') as phone from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_TEL).append("' group by cv_id) h on a.ref_cvid=h.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as email from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_EMAIL).append("' group by cv_id) i on a.ref_cvid=i.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((tag) separator ',') as tag from cv_tag group by cv_id) j on a.ref_cvid=j.cv_id ");
		sql.append(" order by a.create_dt desc ");
		sql.append(" ) comcc where 1=1");
		List values = new ArrayList();
		
		if(isNotEmpty(query.getProjectId())) {
			sql.append(" and  comcc.projectId=? ");
			values.add(query.getProjectId());
		}
		
//		if(isNotEmpty(query.getCompanyId())) {
// 		    sql.append(" and comcc.companyId=? ");
//		    values.add(query.getCompanyId());
//		}
		if(isNotEmpty(query.getRefCvid())) {
			sql.append(" and comcc.refCvid = ? ");
			values.add(query.getRefCvid());			
		}
		if(isNotEmpty(query.getCvId())) {
			sql.append(" and comcc.refCvid = ? ");
			values.add(query.getCvId());			
		}
		if(isNotEmpty(query.getAccount())) {
			sql.append(" and comcc.account = ? ");
			values.add(query.getAccount());			
		}
		if(isNotEmpty(query.getCallStatus())) {
			sql.append(" and comcc.callStatus = ? ");
			values.add(query.getCallStatus());			
		}
		if(isNotEmpty(query.getIsNoCv())) {
			sql.append(" and comcc.refCvid is null ");					
		}
		
		if(isNotEmpty(query.getCallDtBegin())) {
			sql.append(" and comcc.callDt >= ? ");
			values.add(query.getCallDtBegin());			
		}
		if(isNotEmpty(query.getCallDtEnd())) {
			sql.append(" and comcc.callDt < ? ");			
			values.add(DateHelper.add(query.getCallDtEnd(), DateHelper.DAY, 1));			
		}
		
		if(isNotEmpty(query.getCcResult())) {
			sql.append(" and comcc.ccResult = ? ");
			values.add(query.getCcResult());			
		}
		if(isNotEmpty(query.getCcType())) {
			sql.append(" and comcc.ccType = ? ");
			values.add(query.getCcType());			
		}

		if(isNotEmpty(query.getCaller())) {
			sql.append(" and comcc.caller like ? ");
			values.add("%"+query.getCaller()+"%");
		}
		if(isNotEmpty(query.getCallno())) {
			sql.append(" and comcc.callno like ? ");
			values.add("%"+query.getCallno()+"%");
		}
		if(isNotEmpty(query.getCvName())) {
			sql.append(" and comcc.cvName like ? ");
			values.add("%"+query.getCvName()+"%");
		}
		//System.out.println(sql.toString());

		
		//return super.getJdbcDao().createPage(query, values, mapper, pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber()); 
	}
	public Page findPage(SearchProjectCcListQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		
		    Class[] os={SearchProjectCcListQuery.class};
    		Class property= org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
        if (property!=null){
        	 if ("java.lang.String".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(),query.getQuery());
	    			
	    		}
	    		else if ("java.lang.Integer".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));
	    			
	    		}
	    		else if ("java.lang.Double".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
	    		}
	    		else if ("java.util.Date".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
	    		}
        }	 
        //生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
        StringBuilder sql2 = new StringBuilder("select t from SearchProjectCcList t where 1=1 ");
          if(isNotEmpty(query.getId())) {
            sql2.append(" and  t.id = :id ");
        }
        if(isNotEmpty(query.getCallDtBegin())) {
            sql2.append(" and  t.callDt >= :callDtBegin ");
        }
        if(isNotEmpty(query.getCallDtEnd())) {
            sql2.append(" and  t.callDt <= :callDtEnd ");
        }
        if(isNotEmpty(query.getAccount())) {
            sql2.append(" and  t.account like :account ");
            query.setAccount("%"+query.getAccount()+"%"); 

        }
        if(isNotEmpty(query.getCaller())) {
            sql2.append(" and  t.caller like :caller ");
            query.setCaller("%"+query.getCaller()+"%"); 

        }
        if(isNotEmpty(query.getCallno())) {
            sql2.append(" and  t.callno like :callno ");
            query.setCallno("%"+query.getCallno()+"%"); 

        }
          if(isNotEmpty(query.getCallStatus())) {
            sql2.append(" and  t.callStatus = :callStatus ");
        }
          if(isNotEmpty(query.getCcResult())) {
            sql2.append(" and  t.ccResult = :ccResult ");
        }
          if(isNotEmpty(query.getRefCvid())) {
            sql2.append(" and  t.refCvid = :refCvid ");
        }
        if(isNotEmpty(query.getComment())) {
            sql2.append(" and  t.comment like :comment ");
            query.setComment("%"+query.getComment()+"%"); 

        }
        if(isNotEmpty(query.getCreator())) {
            sql2.append(" and  t.creator like :creator ");
            query.setCreator("%"+query.getCreator()+"%"); 

        }
        if(isNotEmpty(query.getCreateDtBegin())) {
            sql2.append(" and  t.createDt >= :createDtBegin ");
        }
        if(isNotEmpty(query.getCreateDtEnd())) {
            sql2.append(" and  t.createDt <= :createDtEnd ");
        }
        if(isNotEmpty(query.getModifier())) {
            sql2.append(" and  t.modifier like :modifier ");
            query.setModifier("%"+query.getModifier()+"%"); 

        }
        if(isNotEmpty(query.getModifiedDtBegin())) {
            sql2.append(" and  t.modifiedDt >= :modifiedDtBegin ");
        }
        if(isNotEmpty(query.getModifiedDtEnd())) {
            sql2.append(" and  t.modifiedDt <= :modifiedDtEnd ");
        }
        if(isNotEmpty(query.getSortColumns())) {
            sql2.append(" order by :sortColumns ");
        }	
        
		return pageQuery(sql2.toString(),query); 
	}
	
	/**
	 * 检查人选是否存在
	 */
	public boolean addCcCheck(Cv cv, List<CvContactDetail> contact_list,
			SearchProjectCcList searchProjectCcList) {
		String cvName = cv.getCvName();  //人选姓名
		System.out.println("人选姓名:"+cvName);
		List<Cv> cvList = this.getHibernateTemplate().find("from Cv cv where cv.cvName=?", cvName);  //判断人选是否存在
		if(cvList.size()>0){
			//CvContactDetail cd = this.getHibernateTemplate()..("from Cv cv where cv.cvName=?", cvName);  //判断人选是否存在
			 List<CvContactDetail> cdList = new ArrayList<CvContactDetail>();//this.getHibernateTemplate().find("from Cv cv where cv.cvName=?", cvName);  //判断人选是否存在
			//判断电话和手机/邮件
			for(Cv cvs : cvList){
				Integer id = cvs.getId();
				 cdList = this.getHibernateTemplate().find("from CvContactDetail cd where cd.cvId=?", id);  //判断人选是否存在
			}
			for(CvContactDetail cds : cdList){
			   String contactType = cds.getContactType();
				for(CvContactDetail cvContact : contact_list){
					if(contactType.equals(ConstantDic.ContactType_TEL) && cvContact.getContactType().equals(ConstantDic.ContactType_TEL)){//电话
						String nbrExt1 = cds.getNbrExt1(); //区号
						String nbr = cds.getNbr(); //电话号码
						String nbrExt2= cds.getNbrExt2(); //分机号
						
						String nbrExt1s = cvContact.getNbrExt1(); //区号
						String nbrs = cvContact.getNbr(); //电话号码
						String nbrExt2s= cvContact.getNbrExt2(); //分机号
						String oldNbr=StringHelper.formatNullValue(nbrExt1, "")+StringHelper.formatNullValue(nbr, "")+StringHelper.formatNullValue(nbrExt2, "");
						String newNbr=StringHelper.formatNullValue(nbrExt1s, "")+StringHelper.formatNullValue(nbrs, "")+StringHelper.formatNullValue(nbrExt2s, "");
						if (oldNbr.equals(newNbr)) return true;
//						if(nbrExt1.equals(nbrExt1) && nbr.equals(nbrs) && nbrExt2.equals(nbrExt2s)){
//							return true;
//						}
					}else if (contactType.equals(ConstantDic.ContactType_EMAIL) && cvContact.getContactType().equals(ConstantDic.ContactType_EMAIL) ){//Email
						String email = cds.getNbr();
						String email2 = cvContact.getNbr();
						if(email.equals(email2)){
							return true;
						}
					}else if (contactType.equals(ConstantDic.ContactType_MOBILE) && cvContact.getContactType().equals(ConstantDic.ContactType_MOBILE) ){//手机
						String mob = cds.getNbr();
						String mobile = cvContact.getNbr();
						if(mob.equals(mobile)){
							return true;
						}
					}
				}
			}
		}else{
			System.out.println("没有人选啦---------------xxxxxxxxxxxxxxxxxxxxxxxxxxx");
		}
		return false;
	}
	

}
